<template><div><h2 id="查询构造器-query-builder" tabindex="-1"><a class="header-anchor" href="#查询构造器-query-builder"><span>查询构造器（Query Builder）</span></a></h2>
<h2 id="简介" tabindex="-1"><a class="header-anchor" href="#简介"><span>简介</span></a></h2>
<p><strong>查询构造器</strong> 建立在 <a href="https://learnku.com/docs/yii-framework/2.0.x/database-access-object-dao/12023" target="_blank" rel="noopener noreferrer">DAO</a> 层基础之上，可让你创建程序化的、DBMS 无关的 SQL 语句。相比于原生 SQL 语句，它可以帮你写出可读性更强的 SQL 相关的代码，并生成安全性更强的 SQL 语句。查询构造器的使用，通常包含以下两个步骤：</p>
<ol>
<li>创建一个 [[yii\db\Query]] 对象来代表一条 SELECT SQL 语句的不同子句（例如 <code v-pre>SELECT</code>, <code v-pre>FROM</code>）；</li>
<li>执行 [[yii\db\Query]] 的一个查询方法（例如：<code v-pre>all()</code>）从数据库当中检索数据。</li>
</ol>
<p>查询构造器的一个典型用法，如下所示：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$rows</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'last_name'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'Smith'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">limit</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">all</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>上面的代码将会生成并执行如下的 SQL 语句，其中 <code v-pre>:last_name</code> 参数绑定了字符串 <code v-pre>'Smith'</code>。</p>
<div class="language-sql line-numbers-mode" data-highlighter="prismjs" data-ext="sql" data-title="sql"><pre v-pre class="language-sql"><code><span class="line"><span class="token keyword">SELECT</span> <span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token identifier"><span class="token punctuation">`</span>email<span class="token punctuation">`</span></span></span>
<span class="line"><span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>user<span class="token punctuation">`</span></span></span>
<span class="line"><span class="token keyword">WHERE</span> <span class="token identifier"><span class="token punctuation">`</span>last_name<span class="token punctuation">`</span></span> <span class="token operator">=</span> :last_name</span>
<span class="line"><span class="token keyword">LIMIT</span> <span class="token number">10</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p>Tip：你平时更多的时候会使用 <code v-pre>yii\db\Query</code> 而不是 <code v-pre>yii\db\QueryBuilder</code>。当你调用其中一个查询方法时，后者将会被前者隐式的调用。<code v-pre>yii\db\QueryBuilder</code> 主要负责将 DBMS 不相关的 <code v-pre>yii\db\Query</code> 对象转换成 DBMS 相关的 SQL 语句（例如，以不同的方式引用表或字段名称）。</p>
</blockquote>
<p>为了创建一个 <code v-pre>yii\db\Query</code> 对象，你需要调用不同的查询构建方法来代表 SQL 语句的不同子句。这些方法的名称集成了在 SQL 语句相应子句中使用的关键字。例如，为了指定 SQL 语句当中的 <code v-pre>FROM</code> 子句，你应该调用 <code v-pre>from()</code> 方法。所有的查询构建器方法返回的是查询对象本身，也就是说，你可以把多个方法的调用串联起来。接下来，我们会对这些查询构建器方法进行一一讲解：</p>
<h2 id="select" tabindex="-1"><a class="header-anchor" href="#select"><span>SELECT</span></a></h2>
<p>[[yii\db\Query::select()]] 方法用来指定 SQL 语句当中的 <code v-pre>SELECT</code> 子句。你可以像下面的例子一样使用一个数组或者字符串来定义需要查询的字段。当 SQL 语句 是由查询对象生成的时候，被查询的字段名称将会自动的被引号括起来。</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$q</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">// 等同于：</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id, email'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">//就像写原生 SQL 语句一样，被选取的字段可以包含表前缀，或字段别名</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'user.id AS user_id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">// 等同于：</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user.id AS user_id, email'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 如果使用数组格式来指定字段，你可以使用数组的键值来表示字段的别名</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'user_id'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'user.id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 可以选择数据库的表达式。当你使用到包含逗号的数据库表达式的时候，你必须使用数组的格式，以避免自动的错误的引号添加。</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string double-quoted-string">"CONCAT(first_name, ' ', last_name) AS full_name"</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>与所有涉及原始 SQL 的地方一样，当在 select 中编写 DB 表达式时，可以对表名和列名使用 <a href="https://learnku.com/docs/yii-framework/2.0.x/database-access-object-dao/12023#3f37f2" target="_blank" rel="noopener noreferrer">与 DBMS 无关的引用语法</a>。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'[[id]]'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你可以调用 [[yii\db\Query::addSelect()]] 方法来选取附加字段，例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'username'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">addSelect</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'email'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如果你在组建查询时没有调用 [[yii\db\Query::select()]] 方法，那么选择的将是 <code v-pre>SELECT *</code> ，也即选取的是所有的字段。</p>
<h3 id="distinct" tabindex="-1"><a class="header-anchor" href="#distinct"><span>DISTINCT</span></a></h3>
<p>你应该调用 [[yii\db\Query::distinct()]] 方法来去除重复行，如下所示：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// SELECT DISTINCT `user_id` ...</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user_id'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">distinct</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="from" tabindex="-1"><a class="header-anchor" href="#from"><span>FROM</span></a></h2>
<p>[[yii\db\Query::from()]] 方法指定了 SQL 语句当中的 <code v-pre>FROM</code> 子句。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$q</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// SELECT * FROM `user`</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 你可以通过字符串或者数组的形式来定义被查询的表名称。就像你写原生的 SQL 语句一样，表名称里面可包含数据库前缀，或表别名</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'public.user u'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'public.post p'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">// 等同于：</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'public.user u, public.post p'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 如果你使用的是数组的格式，那么你同样可以用数组的键值来定义表别名</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'u'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'public.user'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'p'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'public.post'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="表前缀" tabindex="-1"><a class="header-anchor" href="#表前缀"><span>表前缀</span></a></h3>
<p><code v-pre>from</code> 还可以应用默认的 [[yii\db\Connection::$tablePrefix]] 前缀，实现细节请参考 <a href="https://learnku.com/docs/yii-framework/2.0.x/database-access-object-dao/12023#3f37f2" target="_blank" rel="noopener noreferrer">“DAO”的“引用表”章节</a>，例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'[[id]]'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'{{%user}}'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><h2 id="where" tabindex="-1"><a class="header-anchor" href="#where"><span>WHERE</span></a></h2>
<p>[[yii\db\Query::where()]] 方法定义了 SQL 语句当中的 <code v-pre>WHERE</code> 子句。你可以使用如下四种格式来定义 <code v-pre>WHERE</code> 条件：</p>
<ul>
<li>字符串格式，例如：<code v-pre>'status=1'</code></li>
<li>哈希格式，例如： <code v-pre>['status' =&gt; 1, 'type' =&gt; 2]</code></li>
<li>操作符格式，例如：<code v-pre>['like', 'name', 'test']</code></li>
<li>对象格式，例如：<code v-pre>new LikeCondition('name', 'LIKE', 'test')</code></li>
</ul>
<h3 id="字符串格式" tabindex="-1"><a class="header-anchor" href="#字符串格式"><span>字符串格式</span></a></h3>
<p>在定义非常简单的查询条件的时候，字符串格式是最合适的。它看起来和原生 SQL 差不多。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$q</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'status=1'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 或使用参数绑定来绑定动态参数值</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'status=:status'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">':status'</span> <span class="token operator">=></span> <span class="token variable">$status</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 原生 SQL 在日期字段上使用 MySQL YEAR() 函数</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'YEAR(somedate) = 2022'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="防-sql-注入" tabindex="-1"><a class="header-anchor" href="#防-sql-注入"><span>防 SQL 注入</span></a></h4>
<p>千万不要像如下的例子一样直接在条件语句当中嵌入变量，特别是当这些变量来源于终端用户输入的时候，因为这样我们的软件将很容易受到 SQL 注入的攻击。</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// 危险！千万别这样干，除非你非常的确定 $status 是一个整型数值。</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"status=<span class="token interpolation"><span class="token variable">$status</span></span>"</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><p>防 SQL 注入，可以使用 <code v-pre>参数绑定</code>，你可以调用 [[yii\db\Query::params()]] 或 [[yii\db\Query::addParams()]] 方法来分别绑定不同的参数。</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'status=:status'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">addParams</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">':status'</span> <span class="token operator">=></span> <span class="token variable">$status</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><p>与涉及原生 SQL 的所有地方一样，在以字符串格式写入条件时，可以对表名和列名使用 <a href="https://learnku.com/docs/yii-framework/2.0.x/database-access-object-dao/12023#3f37f2" target="_blank" rel="noopener noreferrer">与 DBMS 无关的引用语法</a>，例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'[[status]]=1'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><h3 id="哈希格式" tabindex="-1"><a class="header-anchor" href="#哈希格式"><span>哈希格式</span></a></h3>
<p>哈希格式最适合用来指定多个 <code v-pre>AND</code> 串联起来的简单的等于断句的子条件。它是以数组的形式来书写的，数组的键表示字段的名称，而数组的值则表示 这个字段需要匹配的值。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// 查询构建器非常的智能，能恰当地处理数值当中的空值和数组。</span></span>
<span class="line"><span class="token comment">// ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15))</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'status'</span> <span class="token operator">=></span> <span class="token number">10</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'type'</span> <span class="token operator">=></span> <span class="token constant">null</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'id'</span> <span class="token operator">=></span> <span class="token punctuation">[</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">8</span><span class="token punctuation">,</span> <span class="token number">15</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="防-sql-注入-1" tabindex="-1"><a class="header-anchor" href="#防-sql-注入-1"><span>防 SQL 注入</span></a></h4>
<p>使用哈希格式，Yii 在内部对相应的值进行参数绑定，与字符串格式相比，此处你不需要手动添加参数绑定。但请注意，Yii 不会帮你转义列名，所以如果你从用户端获得的变量作为列名而没有进行任何额外的检查，对于 SQL 注入攻击，你的程序将变得很脆弱。为了保证应用程序的安全，请不要将变量用作列名或者你必须用白名单过滤变量。如果你实在需要从用户获取列名，请阅读 <a href="https://github.com/yiisoft/yii2/blob/2.0.44/docs/guide-zh-CN/output-data-widgets.md#filtering-data" target="_blank" rel="noopener noreferrer">过滤数据</a> 章节。例如，以下代码易受攻击：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// 易受攻击的代码：</span></span>
<span class="line"><span class="token variable">$column</span> <span class="token operator">=</span> <span class="token variable">$request</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'column'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$value</span> <span class="token operator">=</span> <span class="token variable">$request</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'value'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$query</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token variable">$column</span> <span class="token operator">=></span> <span class="token variable">$value</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">// $value 是安全的，但是 $column 名不会被转义处理！</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="操作符格式" tabindex="-1"><a class="header-anchor" href="#操作符格式"><span>操作符格式</span></a></h3>
<p>操作符格式允许你指定类程序风格的任意条件语句，如下所示：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token punctuation">[</span>操作符<span class="token punctuation">,</span> 操作数<span class="token number">1</span><span class="token punctuation">,</span> 操作数<span class="token number">2</span><span class="token punctuation">,</span> <span class="token operator">...</span><span class="token punctuation">]</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>其中每个操作数可以是字符串格式、哈希格式或嵌套的操作符格式，而操作符可以是如下列一个：</p>
<h4 id="and-or" tabindex="-1"><a class="header-anchor" href="#and-or"><span>AND &amp; OR</span></a></h4>
<p><code v-pre>and</code> 操作符，它可以把各个操作数被 SQL 的 <code v-pre>AND</code> 关键字串联起来。<code v-pre>or</code> 用法和 <code v-pre>and</code> 操作符类似，具体如下：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// WHERE id=1 AND id=2</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'and'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'id=1'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'id=2'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 如果操作数是一个数组，它也会按上述规则转换成字符串，这个方法不会自动加引号或者转义</span></span>
<span class="line"><span class="token comment">// WHERE type=1 AND (id=1 OR id=2)</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'and'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'type=1'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'or'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'id=1'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'id=2'</span><span class="token punctuation">]</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="not" tabindex="-1"><a class="header-anchor" href="#not"><span>NOT</span></a></h4>
<p><code v-pre>not</code> 操作符，它只需要操作数 1，它将包含在 SQL 的 <code v-pre>NOT()</code> 中，例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// WHERE NOT(id=1)</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'not'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'id = 1'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 操作数 1 也可以是个描述多个表达式的数组</span></span>
<span class="line"><span class="token comment">// WHERE NOT ((status='draft') AND (name='example'))</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'not'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'status'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'draft'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'name'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'example'</span><span class="token punctuation">]</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="between-not-between" tabindex="-1"><a class="header-anchor" href="#between-not-between"><span>BETWEEN &amp; NOT BETWEEN</span></a></h4>
<p><code v-pre>between</code> 操作符，操作数 1 为字段名称，操作数 2 和 3代表的是这个字段的取值范围。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// WHERE id BETWEEN 1 AND 10</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'between'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><p>如果你需要建立一个值在两列之间的查询条件（比如 <code v-pre>11 BETWEEN min_id AND max_id</code>），你应该使用 [[yii\db\conditions\BetweenColumnsCondition]]。请参阅下一节 <a href="#c4e701">条件-对象格式</a> ，以了解有关条件的对象定义的更多信息。</p>
<p><code v-pre>not between</code> 与 <code v-pre>between</code> 类似，在生成条件时，只是 <code v-pre>BETWEEN</code> 换成 <code v-pre>NOT BETWEEN</code>。</p>
<h4 id="in-not-in" tabindex="-1"><a class="header-anchor" href="#in-not-in"><span>IN &amp; NOT IN</span></a></h4>
<p><code v-pre>in</code> 操作符，操作数 1 应为字段名称或者 DB 表达式。操作数 2 既可以是一个数组，也可以是一个 <code v-pre>Query</code> 对象。它会转换成 <code v-pre>IN</code> 条件语句。如果，操作数 2 是一个数组，那么它代表的是字段或 DB 表达式的取值范围。如果它是 <code v-pre>Query</code> 对象，那么这个子查询的结果集将会作为操作数 1 的字段或者 DB 表达式的取值范围。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// WHERE id IN (1, 2, 3) ，该方法将正确地为字段名加引号以及为取值范围转义</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'in'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">]</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>in</code> 操作符还支持组合字段，此时，操作数 1 应该是一个字段名数组，而操作数 2 应该是一个数组或者 <code v-pre>Query</code> 对象，代表这些字段的取值范围。</p>
<p><code v-pre>not in</code> 用法和 <code v-pre>in</code> 操作符类似，这里就不再赘述。</p>
<h4 id="like" tabindex="-1"><a class="header-anchor" href="#like"><span>LIKE</span></a></h4>
<p><code v-pre>like</code> 操作符，操作数 1 应为一个字段名称或 DB 表达式，操作数 2 可以是字符串或数组，代表第一个操作数需要模糊查询的值。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// WHERE name LIKE '%tester%'</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'like'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'tester'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 如果范围值是一个数组，那么将会生成用 `AND` 串联起来的 多个 `like` 语句</span></span>
<span class="line"><span class="token comment">// WHERE name LIKE '%test%' AND name LIKE '%sample%'</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'like'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'test'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'sample'</span><span class="token punctuation">]</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// WHERE name LIKE '%tester'</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'like'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'%tester'</span><span class="token punctuation">,</span> <span class="token constant boolean">false</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你也可以提供可选的操作数 3 来指定应该如何转义数值当中的特殊字符。该操作数是一个从需要被转义的特殊字符到转义副本的数组映射。如果没有提供这个操作数，将会使用默认的转义映射。<br>
如果需要禁用转义的功能，只需要将参数设置为 <code v-pre>false</code> 或者传入一个空数组即可。需要注意的是，当使用转义映射（又或者没有提供操作数 3 的时候），操作数 2 的值的前后将会被加上 <code v-pre>%</code>。</p>
<p><code v-pre>or like</code>：用法和 <code v-pre>like</code> 操作符类似，区别在于当第二个操作数为数组时，会使用 <code v-pre>OR</code> 来串联多个 <code v-pre>LIKE</code> 条件语句。</p>
<p><code v-pre>not like</code>：用法和 <code v-pre>like</code> 操作符类似，区别在于会使用 <code v-pre>NOT LIKE</code> 来生成条件语句。</p>
<p><code v-pre>or not like</code>：用法和 <code v-pre>not like</code> 操作符类似，区别在于会使用 <code v-pre>OR</code> 来串联多个 <code v-pre>NOT LIKE</code> 条件语句。</p>
<p><code v-pre>ilike</code> 操作符，仅在 PostgreSQL 中支持，可以用于 <code v-pre>ILIKE</code> 语句，该方法对大小写不敏感，详细参考它的 <a href="http://www.postgresql.org/docs/14/static/functions-matching.html#FUNCTIONS-LIKE" target="_blank" rel="noopener noreferrer">文档</a>。</p>
<h4 id="exists-not-exists" tabindex="-1"><a class="header-anchor" href="#exists-not-exists"><span>EXISTS &amp; NOT EXISTS</span></a></h4>
<p><code v-pre>exists</code> 操作符，只需要操作数 1，该操作数必须是代表子查询 <code v-pre>yii\db\Query</code> 的一个实例， 它将会构建一个 <code v-pre>EXISTS (sub-query)</code> 表达式。</p>
<p><code v-pre>not exists</code> 用法和 <code v-pre>exists</code> 操作符类似，它将创建一个 <code v-pre>NOT EXISTS (sub-query)</code> 表达式。</p>
<h4 id="其他操作符" tabindex="-1"><a class="header-anchor" href="#其他操作符"><span>其他操作符</span></a></h4>
<p><code v-pre>=</code>、<code v-pre>&gt;</code>、<code v-pre>&gt;=</code>、<code v-pre>&lt;</code>、<code v-pre>&lt;=</code>、<code v-pre>&lt;&gt;</code>、<code v-pre>!=</code> 或其他包含两个操作数的合法 DB 操作符，操作数 1 必须为字段的名称，而操作数 2 则应为一个值。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// WHERE age>=10</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'>='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'age'</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="防-sql-注入-2" tabindex="-1"><a class="header-anchor" href="#防-sql-注入-2"><span>防 SQL 注入</span></a></h4>
<p>使用操作符格式，Yii 在内部对相应的值进行参数绑定，因此与字符串格式相比，此处你不需要手动添加参数。但请注意，Yii 不会帮你转义列名，所以如果你从用户端获得的变量作为列名而没有进行任何额外的检查，对于 SQL 注入攻击， 你的程序将变得很脆弱。为了保证应用程序的安全，请不要将变量用作列名 或者你必须用白名单过滤变量。如果你实在需要从用户获取列名，请阅读 <a href="https://github.com/yiisoft/yii2/blob/2.0.44/docs/guide-zh-CN/output-data-widgets.md#filtering-data" target="_blank" rel="noopener noreferrer">过滤数据</a> 章节。例如，以下代码易受攻击：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// 易受攻击的代码：</span></span>
<span class="line"><span class="token variable">$column</span> <span class="token operator">=</span> <span class="token variable">$request</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'column'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$value</span> <span class="token operator">=</span> <span class="token variable">$request</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'value'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$query</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token variable">$column</span><span class="token punctuation">,</span> <span class="token variable">$value</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">// $value 是安全的，但是 $column 名不会被转义处理！</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="对象格式" tabindex="-1"><a class="header-anchor" href="#对象格式"><span>对象格式</span></a></h3>
<p>对象格式自 2.0.14 版本开始支持，是定义条件的最强大和最复杂的方法。如果你要在查询构建器上构建自己的抽象方法或者如果你要实现自己的复杂条件，你就会需要它。</p>
<p>查询条件类的实例是不可变的。他们唯一的用途是存储条件数据，并为条件构建器提供 <code v-pre>getters</code> 属性。条件构建器是一个包含转换逻辑的类，它将存储的条件数据转换为 SQL 表达式。</p>
<p>在内部，上面描述的格式在构建 SQL 之前被隐式转换为对象格式，因此可以在单一条件语句下组合适合的格式：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// WHERE type IN (1,2,3) OR name LIKE '%good%' OR disabled=false</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">OrCondition</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token keyword">new</span> <span class="token class-name">InCondition</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'type'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'in'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'like'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'%good%'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'disabled=false'</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="与操作符格式映射" tabindex="-1"><a class="header-anchor" href="#与操作符格式映射"><span>与操作符格式映射</span></a></h4>
<p>操作符格式与对象格式的对应关系是在 [[yii\db\QueryBuilder::conditionClasses]] 属性中定义，这里列举一些比较有代表性的映射关系：</p>
<ul>
<li><code v-pre>AND</code>, <code v-pre>OR</code> -&gt; <code v-pre>yii\db\conditions\ConjunctionCondition</code></li>
<li><code v-pre>NOT</code> -&gt; <code v-pre>yii\db\conditions\NotCondition</code></li>
<li><code v-pre>IN</code>, <code v-pre>NOT IN</code> -&gt; <code v-pre>yii\db\conditions\InCondition</code></li>
<li><code v-pre>BETWEEN</code>, <code v-pre>NOT BETWEEN</code> -&gt; <code v-pre>yii\db\conditions\BetweenCondition</code></li>
</ul>
<p>使用对象格式可以定义自己的条件集，并且可以更容易维护别人定义的条件集。（注：这里是说对象比数组更可靠）更多细节请参考 <a href="#229939">添加自定义查询条件和表达式</a> 小节。</p>
<h3 id="附加条件" tabindex="-1"><a class="header-anchor" href="#附加条件"><span>附加条件</span></a></h3>
<p>你可以使用 [[yii\db\Query::andWhere()]] 或者 [[yii\db\Query::orWhere()]] 在原有条件的基础上附加额外的条件。你可以多次调用这些方法来分别追加不同的条件。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$status</span> <span class="token operator">=</span> <span class="token number">10</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$search</span> <span class="token operator">=</span> <span class="token string single-quoted-string">'yii'</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'status'</span> <span class="token operator">=></span> <span class="token variable">$status</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 如果 $search 不为空成立</span></span>
<span class="line"><span class="token comment">// SQL 就是 WHERE (`status` = 10) AND (`title` LIKE '%yii%')</span></span>
<span class="line"><span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token operator">!</span><span class="token keyword">empty</span><span class="token punctuation">(</span><span class="token variable">$search</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$q</span><span class="token operator">-></span><span class="token function">andWhere</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'like'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'title'</span><span class="token punctuation">,</span> <span class="token variable">$search</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="过滤条件" tabindex="-1"><a class="header-anchor" href="#过滤条件"><span>过滤条件</span></a></h3>
<p>当 <code v-pre>WHERE</code> 条件来自于用户的输入时，你通常需要忽略用户输入的空值。例如，在一个可以通过用户名或者邮箱搜索的表单当中，用户名或者邮箱输入框没有输入任何东西，这种情况下你想要忽略掉对应的搜索条件，那么你就可以使用 [[yii\db\Query::filterWhere()]] 方法来实现：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// $username 和 $email 来自于用户的输入</span></span>
<span class="line"><span class="token comment">// 如果 $email 为空而 $username 不为空</span></span>
<span class="line"><span class="token comment">// WHERE username=:username</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">filterWhere</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token variable">$username</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'email'</span> <span class="token operator">=></span> <span class="token variable">$email</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>[[yii\db\Query::filterWhere()]] 和 [[yii\db\Query::where()]] 唯一的不同就在于，前者 将忽略在条件当中的 <a href="#29e990">哈希格式</a> 的空值。</p>
<blockquote>
<p>Tip: 当一个值为 <code v-pre>null</code>、空数组、空字符串或者一个只包含空格的字符串时，那么它将被判定为空值。</p>
</blockquote>
<h4 id="追加过滤条件" tabindex="-1"><a class="header-anchor" href="#追加过滤条件"><span>追加过滤条件</span></a></h4>
<p>类似于 [[yii\db\Query::andWhere()]] 和 [[yii\db\Query::orWhere()]]，你可以使用 [[yii\db\Query::andFilterWhere()]] 和 [[yii\db\Query::orFilterWhere()]] 方法来追加额外的过滤条件。</p>
<p>此外，[[yii\db\Query::andFilterCompare()]] 可以根据值中的内容智能地确定运算符：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// WHERE name='John Doe'</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">andFilterCompare</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'John Doe'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// WHERE rating>9</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">andFilterCompare</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'rating'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>9'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// WHERE value&lt;=100</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">andFilterCompare</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'value'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'&lt;=100'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>您还可以显式指定运算符：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">andFilterCompare</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'Doe'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'like'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><h2 id="order-by" tabindex="-1"><a class="header-anchor" href="#order-by"><span>ORDER BY</span></a></h2>
<p>[[yii\db\Query::orderBy()]] 方法是用来指定 SQL 语句当中的 <code v-pre>ORDER BY</code> 子句的。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// ... ORDER BY `id` ASC, `name` DESC</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'id'</span> <span class="token operator">=></span> <span class="token constant">SORT_ASC</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'name'</span> <span class="token operator">=></span> <span class="token constant">SORT_DESC</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如上所示，数组当中的键指代的是字段名称，而数组当中的值则表示的是排序的方式。PHP 的常量 <code v-pre>SORT_ASC</code> 指的是升序排列，<code v-pre>SORT_DESC</code> 指的则是降序排列。</p>
<p>如果 <code v-pre>ORDER BY</code> 仅仅包含简单的字段名称，你可以使用字符串来声明它，就像写原生的 SQL 语句一样。例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id ASC, name DESC'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><blockquote>
<p>Note: 当 <code v-pre>ORDER BY</code> 语句包含一些 DB 表达式的时候，你应该使用数组的格式。</p>
</blockquote>
<p>你可以调用 [[yii\db\Query::addOrderBy()]] 来为 <code v-pre>ORDER BY</code> 片断添加额外的子句。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$query</span><span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id ASC'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">addOrderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name DESC'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="group-by" tabindex="-1"><a class="header-anchor" href="#group-by"><span>GROUP BY</span></a></h2>
<p>[[yii\db\Query::groupBy()]] 方法是用来指定 SQL 语句当中的 <code v-pre>GROUP BY</code> 片断的。例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// ... GROUP BY `id`, `status`</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">groupBy</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'status'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><p>如果 <code v-pre>GROUP BY</code> 仅仅包含简单的字段名称，你可以使用字符串来声明它，就像写原生的 SQL 语句一样。例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">groupBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id, status'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><blockquote>
<p>Note: 当 <code v-pre>GROUP BY</code> 语句包含一些 DB 表达式的时候，你应该使用数组的格式。</p>
</blockquote>
<p>你可以调用 [[yii\db\Query::addOrderBy()]] 来为 <code v-pre>GROUP BY</code> 子句添加额外的字段。例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$query</span><span class="token operator">-></span><span class="token function">groupBy</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'status'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">addGroupBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'age'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="having" tabindex="-1"><a class="header-anchor" href="#having"><span>HAVING</span></a></h2>
<p>[[yii\db\Query::having()]] 方法是用来指定 SQL 语句当中的 <code v-pre>HAVING</code> 子句。它带有一个条件，和 <a href="#5105e0">where()</a> 中指定条件的方法一样。例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// ... HAVING `status` = 1</span></span>
<span class="line"><span class="token variable">$query</span><span class="token operator">-></span><span class="token function">having</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'status'</span> <span class="token operator">=></span> <span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><p>请查阅 <a href="#5105e0">where()</a> 的文档来获取更多有关于如何指定一个条件的细节。</p>
<p>你可以调用 [[yii\db\Query::andHaving()]] 或者 [[yii\db\Query::orHaving()]] 方法来为 <code v-pre>HAVING</code> 子句追加额外的条件，例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// ... HAVING (`status` = 1) AND (`age` > 30)</span></span>
<span class="line"><span class="token variable">$query</span><span class="token operator">-></span><span class="token function">having</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'status'</span> <span class="token operator">=></span> <span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">andHaving</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'age'</span><span class="token punctuation">,</span> <span class="token number">30</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>Yii 自 2.0.11 版起 ，提供了 <code v-pre>HAVING</code> 条件的一些构建方法：</p>
<ul>
<li>[[yii\db\Query::filterHaving()]]</li>
<li>[[yii\db\Query::andFilterHaving()]]</li>
<li>[[yii\db\Query::orFilterHaving()]]</li>
</ul>
<h2 id="limit-offset" tabindex="-1"><a class="header-anchor" href="#limit-offset"><span>LIMIT &amp; OFFSET</span></a></h2>
<p>[[yii\db\Query::limit()]] 和 [[yii\db\Query::offset()]] 是用来指定 SQL 语句当中 的 <code v-pre>LIMIT</code> 和 <code v-pre>OFFSET</code> 子句的。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// ... LIMIT 10 OFFSET 20</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">limit</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">offset</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><p>如果你指定了一个无效的 limit 或者 offset（例如，一个负数），那么它将会被忽略掉。</p>
<blockquote>
<p>Tip: 在不支持 <code v-pre>LIMIT</code> 和 <code v-pre>OFFSET</code> 的 DBMS 中（例如，MSSQL），查询构建器将生成一条模拟他们行为的 SQL 语句。</p>
</blockquote>
<h2 id="join" tabindex="-1"><a class="header-anchor" href="#join"><span>JOIN</span></a></h2>
<p>[[yii\db\Query::join()]] 是用来指定 SQL 语句当中的 <code v-pre>JOIN</code> 子句的。例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// ... LEFT JOIN `post` ON `post`.`user_id` = `user`.`id`</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">join</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'LEFT JOIN'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'post'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'post.user_id = user.id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><p>[[yii\db\Query::join()]] 带有四个参数：</p>
<ul>
<li><code v-pre>$type</code>：连接类型，例如，<code v-pre>'INNER JOIN'</code>，<code v-pre>'LEFT JOIN'</code>。</li>
<li><code v-pre>$table</code>：将要连接的表名称。</li>
<li><code v-pre>$on</code>：可选的，连接条件，即 <code v-pre>ON</code> 片段。有关指定条件的详细信息，请参阅 <a href="#5105e0">where()</a>。请注意，数组语法 <strong>不能</strong> 用于指定基于列的条件，例如，<code v-pre>['user.id' =&gt; 'comment.userId']</code> 将导致用户 id 必须等于字符串 <code v-pre>'comment.userId'</code> 的情况。您应该使用字符串语法，并将条件指定为 <code v-pre>'user.id = comment.userId'</code>。</li>
<li><code v-pre>$params</code>：可选参数，与连接条件绑定的参数。</li>
</ul>
<p>你可以分别调用如下的快捷方法来指定 <code v-pre>INNER JOIN</code>, <code v-pre>LEFT JOIN</code> 和 <code v-pre>RIGHT JOIN</code>。</p>
<ul>
<li>[[yii\db\Query::innerJoin()]]</li>
<li>[[yii\db\Query::leftJoin()]]</li>
<li>[[yii\db\Query::rightJoin()]]</li>
</ul>
<p>例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">join</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'LEFT JOIN'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'post'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'post.user_id = user.id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">// 等价于</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">leftJoin</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'post'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'post.user_id = user.id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>可以通过多次调用如上所述的连接方法来连接多张表，每连接一张表调用一次。除了连接表以外，你还可以连接一个子查询。</p>
<h2 id="union" tabindex="-1"><a class="header-anchor" href="#union"><span>UNION</span></a></h2>
<p>[[yii\db\Query::union()]] 方法是用来指定 SQL 语句当中的 <code v-pre>UNION</code> 子句的。例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$q1</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"id, category_id AS type, name"</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'post'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">limit</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$q2</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id, type, name'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">limit</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$q1</span><span class="token operator">-></span><span class="token function">union</span><span class="token punctuation">(</span><span class="token variable">$q2</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你可以通过多次调用 [[yii\db\Query::union()]] 方法来追加更多的 <code v-pre>UNION</code> 子句。</p>
<h2 id="子查询" tabindex="-1"><a class="header-anchor" href="#子查询"><span>子查询</span></a></h2>
<p>从 2.0.1 版本开始，你就可以使用子查询，在定义每一个子查询之时，你应该使用 <code v-pre>yii\db\Query</code> 对象。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$subQuery</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'COUNT(*)'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`</span></span>
<span class="line"><span class="token variable">$q</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'count'</span> <span class="token operator">=></span> <span class="token variable">$subQuery</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'post'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你还可以从子查询中再次查询，例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$subQuery</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'status=1'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u</span></span>
<span class="line"><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'u'</span> <span class="token operator">=></span> <span class="token variable">$subQuery</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你也可以像下面那样在子查询当中使用哈希格式：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$userQuery</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// ...WHERE `id` IN (SELECT `id` FROM `user`)</span></span>
<span class="line"><span class="token variable">$query</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span> <span class="token operator">=></span> <span class="token variable">$userQuery</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>连接子查询。例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$subQuery</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'post'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$query</span><span class="token operator">-></span><span class="token function">leftJoin</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'u'</span> <span class="token operator">=></span> <span class="token variable">$subQuery</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'u.id = author_id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div></div></div><p>在这个例子当中，你应该将子查询放到一个数组当中，而数组当中的键，则为这个子查询的别名。</p>
<h2 id="查询方法" tabindex="-1"><a class="header-anchor" href="#查询方法"><span>查询方法</span></a></h2>
<p>[[yii\db\Query]] 提供了一整套的用于不同查询目的的方法。</p>
<ul>
<li>[[yii\db\Query::all()]]：将返回一个由行组成的数组，每一行是一个由名称和值构成的关联数组（译者注：省略键的数组称为索引数组）。</li>
<li>[[yii\db\Query::one()]]：返回结果集的第一行。</li>
<li>[[yii\db\Query::column()]]：返回结果集的第一列。</li>
<li>[[yii\db\Query::scalar()]]：返回结果集的第一行第一列的标量值。</li>
<li>[[yii\db\Query::exists()]]：返回一个表示该查询是否包结果集的值。</li>
<li>[[yii\db\Query::count()]]：返回 <code v-pre>COUNT</code> 查询的结果。</li>
<li>其它集合查询方法：包括 [[yii\db\Query::sum()]], [[yii\db\Query::average()]], [[yii\db\Query::max()]], [[yii\db\Query::min()]] 等。他们都要一个必选参数，既可以是一个字段名称，又可以是一个 DB 表达式。</li>
</ul>
<p>例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// SELECT `id`, `email` FROM `user`</span></span>
<span class="line"><span class="token variable">$rows</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">all</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// SELECT * FROM `user` WHERE `username` LIKE `%test%`</span></span>
<span class="line"><span class="token variable">$row</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'like'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'username'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'test'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">one</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p>Note: [[yii\db\Query::one()]] 方法只返回查询结果当中的第一条数据，条件语句中不会加上 <code v-pre>LIMIT 1</code> 条件。如果你清楚的知道查询将会只返回一行或几行数据（例如，如果你是通过某些主键来查询的），这很好也提倡这样做。但是，如果查询结果 有机会返回大量的数据时，那么你应该显示调用 <code v-pre>limit(1)</code> 方法，以改善性能。 例如，<code v-pre>(new \yii\db\Query())-&gt;from('user')-&gt;limit(1)-&gt;one()</code>。</p>
</blockquote>
<p>所有的这些查询方法都有一个可选的参数 <code v-pre>$db</code>, 该参数指代的是 <code v-pre>yii\db\Connection</code>，执行一个 DB 查询时会用到。如果你省略了这个参数，那么 <code v-pre>db</code> 应用组件将会被用作默认的 DB 连接。 如下是另外一个使用 <code v-pre>count()</code> 查询的例子：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// 执行 SQL: SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name</span></span>
<span class="line"><span class="token variable">$count</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'last_name'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'Smith'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">count</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="运行机制" tabindex="-1"><a class="header-anchor" href="#运行机制"><span>运行机制</span></a></h3>
<p>当你调用 <code v-pre>yii\db\Query</code> 当中的一个查询方法的时候，实际上内在的运作机制如下：</p>
<ul>
<li>在当前 <code v-pre>yii\db\Query</code> 的构造基础之上，调用 <code v-pre>yii\db\QueryBuilder</code> 来生成一条 SQL 语句；</li>
<li>利用生成的 SQL 语句创建一个 <code v-pre>yii\db\Command</code> 对象；</li>
<li>调用 <code v-pre>yii\db\Command</code> 的查询方法（例如，<code v-pre>queryAll()</code>）来执行这条 SQL 语句，并检索数据。</li>
</ul>
<h3 id="打印-sql" tabindex="-1"><a class="header-anchor" href="#打印-sql"><span>打印 SQL</span></a></h3>
<p>有时候，你也许想要测试或者使用一个由 <code v-pre>yii\db\Query</code> 对象创建的 SQL 语句。你可以使用以下的代码来达到目的：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$command</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'last_name'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'Smith'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">limit</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 打印 SQL 语句</span></span>
<span class="line"><span class="token keyword">echo</span> <span class="token variable">$command</span><span class="token operator">-></span><span class="token property">sql</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">// 打印被绑定的参数</span></span>
<span class="line"><span class="token function">print_r</span><span class="token punctuation">(</span><span class="token variable">$command</span><span class="token operator">-></span><span class="token property">params</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 返回查询结果的所有行</span></span>
<span class="line"><span class="token variable">$rows</span> <span class="token operator">=</span> <span class="token variable">$command</span><span class="token operator">-></span><span class="token function">queryAll</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="索引查询结果" tabindex="-1"><a class="header-anchor" href="#索引查询结果"><span>索引查询结果</span></a></h3>
<p>当你在调用 [[yii\db\Query::all()]] 方法时，它将返回一个以连续的整型数值为索引的数组。 而有时候，你可能希望使用一个特定的字段或者表达式的值来作为索引结果集数组。那么你可以在调用 [[yii\db\Query::all()]] 之前使用 [[yii\db\Query::indexBy()]] 方法来达到这个目的。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// 返回 [100 => ['id' => 100, 'username' => '...', ...], 101 => [...], 103 => [...], ...]</span></span>
<span class="line"><span class="token variable">$query</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">limit</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">indexBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">all</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如需使用表达式的值做为索引，那么只需要传递一个匿名函数给 [[yii\db\Query::indexBy()]] 方法即可，例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$query</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">indexBy</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$row</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">        <span class="token keyword">return</span> <span class="token variable">$row</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">]</span> <span class="token operator">.</span> <span class="token variable">$row</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'username'</span><span class="token punctuation">]</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">all</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>该匿名函数将带有一个包含了当前行的数据的 <code v-pre>$row</code> 参数，并且返回用作当前行索引的标量值（译者注：就是简单的数值或者字符串，而不是其他复杂结构，例如数组）。</p>
<blockquote>
<p>Note: 与 [[yii\db\Query::groupBy()]] 或者 [[yii\db\Query::orderBy()]] 等查询方法不同，他们将转换为 SQL 查询语句的一部分，而这个方法（indexBy）在从数据库取回数据后才生效执行的。这意味着只能使用那些在你的 SELECT 查询中的列名。此外，你用表名连接取列名的时候，比如 <code v-pre>customer.id</code>，结果中将只包含 <code v-pre>id</code> 列，因此你必须调用 <code v-pre>-&gt;indexBy('id')</code> 不要带表名前缀。</p>
</blockquote>
<h3 id="批处理查询" tabindex="-1"><a class="header-anchor" href="#批处理查询"><span>批处理查询</span></a></h3>
<p>当需要处理大数据的时候，像 [[yii\db\Query::all()]] 这样的方法就不太合适了，因为它们会把所有查询的数据都读取到客户端内存上。为了解决这个问题，Yii 提供了批处理查询的支持。服务端先保存查询结果，然后客户端使用游标（cursor）每次迭代出固定的一批结果集回来。</p>
<blockquote>
<p>Warning: MySQL 批处理查询的实现存在已知的局限性和变通方法。见下文。</p>
</blockquote>
<p>批处理查询的用法如下：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$q</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">batch</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token variable">$users</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// $users 是一个包含100条或小于100条用户表数据的数组</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// or to iterate the row one by one</span></span>
<span class="line"><span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">each</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token variable">$user</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// 数据从服务端中以 100 个为一组批量获取，</span></span>
<span class="line">    <span class="token comment">// 但是 $user 代表 user 表里的一行数据</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>[[yii\db\Query::batch()]] 和 [[yii\db\Query::each()]] 方法将会返回一个实现了<code v-pre>Iterator</code> 接口 [[yii\db\BatchQueryResult]] 的对象，可以用在 <code v-pre>foreach</code> 结构当中使用。在第一次迭代取数据的时候，数据库会执行一次 SQL 查询，然后在剩下的迭代中，将直接从结果集中批量获取数据。默认情况下，一批的大小为 100，也就意味着一批获取的数据是 100 行。你可以通过给 <code v-pre>batch()</code> 或者 <code v-pre>each()</code> 方法的第一个参数传值来改变每批行数的大小。</p>
<p>相对于 [[yii\db\Query::all()]] 方法，批处理查询每次只读取 100 行的数据到内存。</p>
<p>如果你通过 [[yii\db\Query::indexBy()]] 方法为查询结果指定了索引字段，那么批处理查询将仍然保持相对应的索引方案，例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$q</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Query</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">indexBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'username'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">batch</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token variable">$users</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// $users 的 “username” 字段将会成为索引</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">each</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token variable">$username</span> <span class="token operator">=></span> <span class="token variable">$user</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="mysql中批量查询的局限性" tabindex="-1"><a class="header-anchor" href="#mysql中批量查询的局限性"><span>MySQL中批量查询的局限性</span></a></h4>
<p>MySQL 是通过 PDO 驱动库实现批量查询的。默认情况下，MySQL 查询是 <a href="https://www.php.net/manual/zh/mysqlinfo.concepts.buffering.php" target="_blank" rel="noopener noreferrer"><code v-pre>带缓存的</code></a>，这违背了使用游标（cursor）获取数据的目的，因为它不阻止驱动程序将整个结果集加载到客户端的内存中。</p>
<blockquote>
<p>Note: 当使用 <code v-pre>libmysqlclient</code> 时（PHP5 的标配），计算 PHP 的内存限制时，用于数据结果集的内存不会计算在内。看上去批量查询是正确运行的，实际上整个数据集都被加载到了客户端的内存中，而且这个使用量可能还会再增长。</p>
</blockquote>
<p>要禁用缓存并减少客户端内存的需求量，PDO 连接属性 <code v-pre>PDO::MYSQL_ATTR_USE_BUFFERED_QUERY</code> 必须设置为 <code v-pre>false</code>。这样，直到整个数据集被处理完毕前，通过此连接是无法创建其他查询的。这样的操作可能会阻碍 <code v-pre>ActiveRecord</code> 执行表结构查询。如果这不构成问题（表结构已被缓存过了），我们可以通过切换原本的连接到非缓存模式，然后在批量查询完成后再切换回来。</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token property">pdo</span><span class="token operator">-></span><span class="token function">setAttribute</span><span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified static-context"><span class="token punctuation">\</span>PDO</span><span class="token operator">::</span><span class="token constant">MYSQL_ATTR_USE_BUFFERED_QUERY</span><span class="token punctuation">,</span> <span class="token constant boolean">false</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 执行批量查询</span></span>
<span class="line"></span>
<span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token property">pdo</span><span class="token operator">-></span><span class="token function">setAttribute</span><span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified static-context"><span class="token punctuation">\</span>PDO</span><span class="token operator">::</span><span class="token constant">MYSQL_ATTR_USE_BUFFERED_QUERY</span><span class="token punctuation">,</span> <span class="token constant boolean">true</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p>Note: 对于 MyISAM，在执行批量查询的过程中，表可能将被锁，将延迟或拒绝其他连接的写入操作。当使用非缓存查询时，尽量缩短游标打开的时间。</p>
</blockquote>
<p>如果表结构没有被缓存，或在批量查询被处理过程中需要执行其他查询，你可以创建一个单独的非缓存链接到数据库：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$unbufferedDb</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Connection</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token property">dsn</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token property">username</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'password'</span> <span class="token operator">=></span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token property">password</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'charset'</span> <span class="token operator">=></span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token property">charset</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$unbufferedDb</span><span class="token operator">-></span><span class="token function">open</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$unbufferedDb</span><span class="token operator">-></span><span class="token property">pdo</span><span class="token operator">-></span><span class="token function">setAttribute</span><span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified static-context"><span class="token punctuation">\</span>PDO</span><span class="token operator">::</span><span class="token constant">MYSQL_ATTR_USE_BUFFERED_QUERY</span><span class="token punctuation">,</span> <span class="token constant boolean">false</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如果你除了 <code v-pre>PDO::MYSQL_ATTR_USE_BUFFERED_QUERY</code> 是 <code v-pre>false</code> 之外， 要确保 <code v-pre>$unbufferedDb</code> 拥有和原来缓存 <code v-pre>$db</code> 完全一样的属性，请参阅 <a href="https://github.com/yiisoft/yii2/issues/8420#issuecomment-301423833" target="_blank" rel="noopener noreferrer">实现 <code v-pre>$db</code> 的深度拷贝</a>，手动方法将它设置为 false 即可。</p>
<p>然后使用此连接正常创建查询，新连接用于运行批量查询，逐条或批量进行结果处理：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// 获取 1000 为一组的批量数据</span></span>
<span class="line"><span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">batch</span><span class="token punctuation">(</span><span class="token number">1000</span><span class="token punctuation">,</span> <span class="token variable">$unbufferedDb</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token variable">$users</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 每次从服务端批量获取1000个数据，但是逐个遍历进行处理</span></span>
<span class="line"><span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$q</span><span class="token operator">-></span><span class="token function">each</span><span class="token punctuation">(</span><span class="token number">1000</span><span class="token punctuation">,</span> <span class="token variable">$unbufferedDb</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token variable">$user</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>当结果集已处理完毕不再需要连接时，可以关闭它：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$unbufferedDb</span><span class="token operator">-></span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><blockquote>
<p>Note: 非缓存查询在 PHP 端使用更少的缓存，但会增加 MySQL 服务器端的负载。建议您使用生产实践设计自己的代码以获取额外的海量数据，<a href="https://github.com/yiisoft/yii2/issues/8420#issuecomment-296109257" target="_blank" rel="noopener noreferrer">例如，将数字键分段，使用非缓存的查询遍历</a>。</p>
</blockquote>
<h3 id="添加自定义查询条件和表达式" tabindex="-1"><a class="header-anchor" href="#添加自定义查询条件和表达式"><span>添加自定义查询条件和表达式</span></a></h3>
<p>我们在 <a href="#c4e701">查询条件-对象格式</a> 小节中提到过，可以创建自定义的查询条件类。例如，我们需要创建一个查询条件，它可以检查某些字段小于特定值的情况。当使用操作符格式时，代码如下：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'and'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'posts'</span><span class="token punctuation">,</span> <span class="token variable">$minLimit</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'comments'</span><span class="token punctuation">,</span> <span class="token variable">$minLimit</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'reactions'</span><span class="token punctuation">,</span> <span class="token variable">$minLimit</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'subscriptions'</span><span class="token punctuation">,</span> <span class="token variable">$minLimit</span></span>
<span class="line"><span class="token punctuation">]</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>当这样的查询条件仅被应用一次，没什么问题。当它在一个查询语句中被多次使用时，就有很多优化点了。我们创建一个自定义查询条件对象来证实它。</p>
<p>Yii 有 [[yii\db\conditions\ConditionInterface]] 接口类，必须用它来标识这是一个表示查询条件的类。它需要实现 <code v-pre>fromArrayDefinition()</code> 方法，用来从数组格式创建查询条件。如果我们不需要它，抛出一个异常来完成此方法即可。</p>
<p>创建自定义查询条件类，我们就可以构建最适合当前需求的 API。</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">namespace</span> <span class="token package">app<span class="token punctuation">\</span>db<span class="token punctuation">\</span>conditions</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">class</span> <span class="token class-name-definition class-name">AllGreaterCondition</span> <span class="token keyword">implements</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>conditions<span class="token punctuation">\</span>ConditionInterface</span></span>
<span class="line"><span class="token punctuation">{</span></span>
<span class="line">    <span class="token keyword">private</span> <span class="token variable">$columns</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token keyword">private</span> <span class="token variable">$value</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line">    <span class="token doc-comment comment">/**</span>
<span class="line">     * <span class="token keyword">@param</span> <span class="token class-name"><span class="token keyword">string</span><span class="token punctuation">[</span><span class="token punctuation">]</span></span> <span class="token parameter">$columns</span> 要大于 $value 的字段名数组</span>
<span class="line">     * <span class="token keyword">@param</span> <span class="token class-name"><span class="token keyword">mixed</span></span> <span class="token parameter">$value</span> 每个 $column 要比较的数值</span>
<span class="line">     */</span></span>
<span class="line">    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">__construct</span><span class="token punctuation">(</span><span class="token keyword type-hint">array</span> <span class="token variable">$columns</span><span class="token punctuation">,</span> <span class="token variable">$value</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token punctuation">{</span></span>
<span class="line">        <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">columns</span> <span class="token operator">=</span> <span class="token variable">$columns</span><span class="token punctuation">;</span></span>
<span class="line">        <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">value</span> <span class="token operator">=</span> <span class="token variable">$value</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token punctuation">}</span></span>
<span class="line"></span>
<span class="line">    <span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">function</span> <span class="token function-definition function">fromArrayDefinition</span><span class="token punctuation">(</span><span class="token variable">$operator</span><span class="token punctuation">,</span> <span class="token variable">$operands</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token punctuation">{</span></span>
<span class="line">        <span class="token keyword">throw</span> <span class="token keyword">new</span> <span class="token class-name">InvalidArgumentException</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'Not implemented yet, but we will do it later'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token punctuation">}</span></span>
<span class="line"></span>
<span class="line">    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">getColumns</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">return</span> <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">columns</span><span class="token punctuation">;</span> <span class="token punctuation">}</span></span>
<span class="line">    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">getValue</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">return</span> <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">vaule</span><span class="token punctuation">;</span> <span class="token punctuation">}</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>我们现在创建了一个查询条件对象：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$conditon</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">AllGreaterCondition</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'col1'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'col2'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token number">42</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>但是 <code v-pre>QueryBuilder</code> 还不知道怎样从此对象生成 SQL 查询条件。因此我们还需要为这个条件对象创建一个构建器。这个构建器必须实现 [[yii\db\ExpressionBuilderInterface]] 接口和 <code v-pre>build()</code> 方法。</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">namespace</span> <span class="token package">app<span class="token punctuation">\</span>db<span class="token punctuation">\</span>conditions</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">class</span> <span class="token class-name-definition class-name">AllGreaterConditionBuilder</span> <span class="token keyword">implements</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>ExpressionBuilderInterface</span></span>
<span class="line"><span class="token punctuation">{</span></span>
<span class="line">    <span class="token keyword">use</span> <span class="token package"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>ExpressionBuilderTrait</span><span class="token punctuation">;</span> <span class="token comment">// Contains constructor and `queryBuilder` property.</span></span>
<span class="line"></span>
<span class="line">    <span class="token doc-comment comment">/**</span>
<span class="line">     * <span class="token keyword">@param</span> <span class="token class-name">ExpressionInterface</span> <span class="token parameter">$condition</span> 要构建的查询条件对象</span>
<span class="line">     * <span class="token keyword">@param</span> <span class="token class-name"><span class="token keyword">array</span></span> <span class="token parameter">$params</span> 绑定的参数</span>
<span class="line">     * <span class="token keyword">@return</span> <span class="token class-name">AllGreaterCondition</span></span>
<span class="line">     */</span></span>
<span class="line">    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">build</span><span class="token punctuation">(</span><span class="token class-name type-declaration">ExpressionInterface</span> <span class="token variable">$expression</span><span class="token punctuation">,</span> <span class="token keyword type-declaration">array</span> <span class="token operator">&amp;</span><span class="token variable">$params</span> <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token punctuation">{</span></span>
<span class="line">        <span class="token variable">$value</span> <span class="token operator">=</span> <span class="token variable">$condition</span><span class="token operator">-></span><span class="token function">getValue</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line">        <span class="token variable">$conditions</span> <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token punctuation">;</span></span>
<span class="line">        <span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$expression</span><span class="token operator">-></span><span class="token function">getColumns</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token variable">$column</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">            <span class="token variable">$conditions</span><span class="token punctuation">[</span><span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">SimpleCondition</span><span class="token punctuation">(</span><span class="token variable">$column</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token variable">$value</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">        <span class="token punctuation">}</span></span>
<span class="line"></span>
<span class="line">        <span class="token keyword">return</span> <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">queryBuilder</span><span class="token operator">-></span><span class="token function">buildCondition</span><span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">AndCondition</span><span class="token punctuation">(</span><span class="token variable">$conditions</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token variable">$params</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token punctuation">}</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>接下来，让 <code v-pre>yii\db\QueryBuilder</code> 知道我们的新查询条件对象，添加一个映射到 <code v-pre>expressionBuilders</code> 数组中，在应用配置中完成即可：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token string single-quoted-string">'db'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'class'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'yii\db\mysql\Connection'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line">    <span class="token string single-quoted-string">'queryBuilder'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token string single-quoted-string">'expressionBuilders'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">            <span class="token string single-quoted-string">'app\db\conditions\AllGreaterCondition'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'app\db\conditions\AllGreaterConditionBuilder'</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>现在我们可以在 <code v-pre>where()</code> 中使用此查询条件对象了：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$query</span><span class="token operator">-></span><span class="token function">andWhere</span><span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">AllGreaterCondition</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'posts'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'comments'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'reactions'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'subscriptions'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token variable">$minValue</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>如果我们想要自定义操作符查询条件，可以在 [[yii\db\QueryBuilder::conditionClasses]] 中 这样声明：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token string single-quoted-string">'db'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'class'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'yii\db\mysql\Connection'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line">    <span class="token string single-quoted-string">'queryBuilder'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token string single-quoted-string">'expressionBuilders'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">            <span class="token string single-quoted-string">'app\db\conditions\AllGreaterCondition'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'app\db\conditions\AllGreaterConditionBuilder'</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token string single-quoted-string">'conditionClasses'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">            <span class="token string single-quoted-string">'ALL>'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'app\db\conditions\AllGreaterCondition'</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>并在 <code v-pre>app\db\conditions\AllGreaterCondition</code> 对象中实现 <code v-pre>fromArrayDefinition()</code> 静态方法：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">namespace</span> <span class="token package">app<span class="token punctuation">\</span>db<span class="token punctuation">\</span>conditions</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">class</span> <span class="token class-name-definition class-name">AllGreaterCondition</span> <span class="token keyword">implements</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>conditions<span class="token punctuation">\</span>ConditionInterface</span></span>
<span class="line"><span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// ... 这里省略其他方法的实现</span></span>
<span class="line"></span>
<span class="line">    <span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">function</span> <span class="token function-definition function">fromArrayDefinition</span><span class="token punctuation">(</span><span class="token variable">$operator</span><span class="token punctuation">,</span> <span class="token variable">$operands</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token punctuation">{</span></span>
<span class="line">        <span class="token keyword">return</span> <span class="token keyword">new</span> <span class="token keyword">static</span><span class="token punctuation">(</span><span class="token variable">$operands</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token variable">$operands</span><span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token punctuation">}</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>然后呢，我们就可以使用更简短的操作符格式来创建自定义查询条件了：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$query</span><span class="token operator">-></span><span class="token function">andWhere</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'ALL>'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'posts'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'comments'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'reactions'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'subscriptions'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token variable">$minValue</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>你可能注意到了，这里使用到了两个概念：表达式对象和条件对象。表达式对象实现了 <code v-pre>yii\db\ExpressionInterface</code> 接口， 它还依赖于一个表达式构建器来执行构建逻辑，而表达式构建器实现了 <code v-pre>yii\db\ExpressionBuilderInterface</code> 接口。而条件对象实现了 <code v-pre>yii\db\condition\ConditionInterface</code> 接口，它是继承了 <code v-pre>yii\db\ExpressionInterface</code> 接口，如上面例子所示，它用于数组定义的条件的场景，当然条件对象也需要构建器。</p>
<p>总结起来就是:</p>
<ul>
<li>
<p>Expression – 表达式对象，是数据集的数据转换对象（DTO），它可以被编译为一些特定 SQL 语句（操作符、字符串、数组、JSON等）。</p>
</li>
<li>
<p>Condition – 条件对象，是表达式对象超集，它可以聚合多个表达式对象（或标量值），然后编译成一条 SQL 查询条件。</p>
</li>
</ul>
<p>你可以创建自己的类来实现 <code v-pre>yii\db\ExpressionInterface</code> 接口，达到封装的目的，即隐藏复杂的 SQL 语句拼装过程。想学习到更多关于表达式对象的实践 请听下回分解。</p>
<blockquote>
<p>💖喜欢本文档的，欢迎点赞、收藏、留言或转发，谢谢支持！<br>
作者邮箱：zhuzixian520@126.com，github地址：<a href="https://github.com/zhuzixian520" target="_blank" rel="noopener noreferrer">github.com/zhuzixian520</a></p>
</blockquote>
</div></template>


